if exists (select * from dbo.sysobjects where id = 
object_id(N'[sp_GetRepliesByUserID]') and OBJECTPROPERTY(id, N'IsProcedure') 
= 1)
   drop procedure [sp_GetRepliesByUserID]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

-- setuser N'springofwat'
-- GO

CREATE PROCEDURE sp_GetRepliesByUserID
@UserID int,
@StartIndex int,
@NumberOfRecords int,
@Type  varchar(10),
@Status varchar (10),
@CategoryID int,
@OrderBy varchar(20),
@TotalRecords int output
AS
DECLARE @SelectSQL  VARCHAR(4000),
                 @WHERE       INT

SELECT @WHERE = 0
SELECT @SelectSQL = 'SELECT T1.PostID, T1.Subject, T2.* FROM ForumPosts as 
T1  inner  join forumposts as T2 on T1.postid = T2.parentpostid'

IF ISNULL(@UserID, 0) > 0
BEGIN
  SELECT @SelectSQL = @SelectSQL + ' WHERE T1.UserID = ' + CAST(@UserID AS 
VARCHAR(20))
  SET @WHERE = 1
END

IF ISNULL(@CategoryId, 0) > 0
IF @WHERE = 0
BEGIN
  SELECT @SelectSQL = @SelectSQL + ' WHERE T2.CategoryId = ' + 
CAST(@CategoryId AS VARCHAR(20))
  SET @WHERE = 1
END ELSE
BEGIN
  SELECT @SelectSQL = @SelectSQL + ' AND T2.CategoryId = ' + 
CAST(@CategoryId AS VARCHAR(20))
END

IF LEN(ISNULL(@Status, '')) > 0
IF @WHERE = 0
BEGIN
      SELECT @SelectSQL = @SelectSQL + ' WHERE T2.Status = ''' + @Status + 
''''
      SET @WHERE = 1
END ELSE
      SELECT @SelectSQL = @SelectSQL + ' AND T2.Status =''' + @Status + ''''

IF LEN(ISNULL(@Type, '')) > 0
IF @WHERE = 0
BEGIN
      SELECT @SelectSQL = @SelectSQL + ' WHERE T2.Type =''' + @Type + ''''
      SET @WHERE = 1
END ELSE
      SELECT @SelectSQL = @SelectSQL + ' AND T2.Type = ''' + @Type + ''''



SELECT  @SelectSQL = @SelectSQL + ' Order By T2.' + @OrderBy

print   @SelectSQL

/* This is bad that we have to list all the column definition here */
/* I tried to use 'select into #tempposts from forumposts' and    */
/* 'alter table #tempposts alter column postid int (4)' but didn't  */
/* work.                                                                     
                */
/* The main idea is to add a new identity column rownum. That*/
/* is all because T-SQL does not provide rownum column        */

create table #tempposts (rownum int identity(1,1),
                         OriginalPostID int,
                         OriginalSubject nvarchar (200),
	                 PostID int,
		    CategoryID int,
		    ParentPostID int,
		    Subject nvarchar (200),
		    Body ntext,
		    CreateDate datetime,
		    Status varchar (10),
		    Summary nvarchar (2000),
                              Type varchar (50),
                               UserID int,
                               umberOfVisits int
                       ,PartnerID int
                       ,OriginAuthor nvarchar(400)
                       ,URL varchar(1000)
                       ,OriginalDate datetime
                       ,LastModifiedBy int
                       ,LastModifiedDateTime datetime)

insert into #tempposts (OriginalPostID,
		     OriginalSubject,
    		     PostID,
		    CategoryID,
		    ParentPostID,
		    Subject,
		    Body,
		    CreateDate,
		    Status,
		    Summary,
                              Type,
                              UserID,
                              umberOfVisits
                      ,PartnerID
                      ,OriginAuthor
                      ,URL
                      ,OriginalDate
                      ,LastModifiedBy
                      ,LastModifiedDateTime)
                 EXEC(@SelectSQL)

select @TotalRecords = count(*) from #tempposts

IF ISNULL(@NumberOfRecords, 0) > 0
    select * from #tempposts where rownum >= @StartIndex  And rownum  < 
@StartIndex + @NumberOfRecords
ELSE
    select * from #tempposts where rownum >= @StartIndex

Drop table #tempposts
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
sp_GetRepliesByUserID  71, 1, 10, 'bbs', null, 40, 'categoryid', 0
*/

